package es.udc.emarketapp.emarket.model.country.dao;

import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import es.udc.emarketapp.emarket.model.country.vo.CountryVO;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;

public class MySQLCountryDAO implements SQLCountryDAO {

      public CountryVO findById(Connection connection, String idPais)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT nom_pais "
                    + "FROM pais  WHERE id_pais = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idPais);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idPais,
                        CountryVO.class.getName());
            }

            // Get results.

            String nomPais = resultSet.getString(1);

            // Return the value object.
            return new CountryVO(idPais, nomPais);

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findById


    public CountryVO findById(Connection connection, String idPais, String lang)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT nom_pais_" + lang
                    + " FROM pais  WHERE id_pais = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idPais);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idPais,
                        CountryVO.class.getName());
            }

            // Get results.

            String nomPais = resultSet.getString(1);

            // Return the value object.
            return new CountryVO(idPais, nomPais);

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findById

   
    public String findIdByName(Connection connection, String nomPais)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_pais "
                    + "FROM pais  WHERE nom_pais = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, nomPais);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(nomPais,
                        CountryVO.class.getName());
            }

            // Get results.

            String idPais = resultSet.getString(1);

            // Return the value object.
            return idPais;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findIdByName

    public List<CountryVO> findAllCountries(Connection connection,
            int startIndex, int count) throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_pais, nom_pais "
                    + "FROM pais ";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);


            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<CountryVO> countryVOs = new ArrayList<CountryVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    String idPais = resultSet.getString(i++);
                    String nomPais = resultSet.getString(i++);

                    countryVOs.add(new CountryVO(idPais, nomPais));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return countryVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

   }//findAllCountries

    public List<CountryVO> findAllCountries(Connection connection, String lang,
            int startIndex, int count) throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_pais, nom_pais_" + lang
                    + " FROM pais ";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);


            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<CountryVO> countryVOs = new ArrayList<CountryVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    String idPais = resultSet.getString(i++);
                    String nomPais = resultSet.getString(i++);

                    countryVOs.add(new CountryVO(idPais, nomPais));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return countryVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

   }//findAllCountries


    
public List<CountryVO> findAvaliableCountriesToSpecifyShipping(Connection connection,
            String idVendedor, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_pais, nom_pais " +
                    " FROM pais WHERE id_pais NOT IN(SELECT id_pais FROM " +
                    "envia WHERE id_vendedor = ? )";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);


            // Fill "preparedStatement".
            preparedStatement.setString(1, idVendedor);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<CountryVO> countryVOs = new ArrayList<CountryVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    String idPais = resultSet.getString(i++);
                    String nomPais = resultSet.getString(i++);

                    countryVOs.add(new CountryVO(idPais, nomPais));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return countryVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

   }//findAvaliableCountriesToSpecifyShipping

public List<CountryVO> findAvaliableCountriesToSpecifyShipping(Connection connection,
            String idVendedor, String lang, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_pais, nom_pais_" + lang +
                    " FROM pais WHERE id_pais NOT IN(SELECT id_pais FROM " +
                    "envia WHERE id_vendedor = ? )";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);


            // Fill "preparedStatement".
            preparedStatement.setString(1, idVendedor);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<CountryVO> countryVOs = new ArrayList<CountryVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    String idPais = resultSet.getString(i++);
                    String nomPais = resultSet.getString(i++);

                    countryVOs.add(new CountryVO(idPais, nomPais));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return countryVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

   }//findAvaliableCountriesToSpecifyShipping



        public List<CountryVO> findShippingCountries(Connection connection,
            String idVendedor, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_pais, nom_pais " +
                    "FROM pais WHERE id_pais IN(SELECT DISTINCT id_pais FROM " +
                    "envia WHERE id_vendedor = ? )";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);


            // Fill "preparedStatement".
            preparedStatement.setString(1, idVendedor);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<CountryVO> countryVOs = new ArrayList<CountryVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    String idPais = resultSet.getString(i++);
                    String nomPais = resultSet.getString(i++);

                    countryVOs.add(new CountryVO(idPais, nomPais));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return countryVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

   }//findShippingCountries

           public List<CountryVO> findShippingCountries(Connection connection,
            String idVendedor, String lang, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_pais, nom_pais_"+ lang +
                    " FROM pais WHERE id_pais IN(SELECT DISTINCT id_pais FROM " +
                    "envia WHERE id_vendedor = ? )";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);


            // Fill "preparedStatement".
            preparedStatement.setString(1, idVendedor);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<CountryVO> countryVOs = new ArrayList<CountryVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    String idPais = resultSet.getString(i++);
                    String nomPais = resultSet.getString(i++);

                    countryVOs.add(new CountryVO(idPais, nomPais));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return countryVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

   }//findShippingCountries

      
}
